import openpyxl
import os

#创建新表格
new_workbook = openpyxl.Workbook()
#新表格的默认sheet叫作Sheet
new_sheet = new_workbook['Sheet']
#写表头
new_sheet['A1'] = '电商平台'
new_sheet['B1'] = '品牌'
new_sheet['C1'] = '评论'
new_sheet['D1'] = '时间'
new_sheet['E1'] = '型号'

#新表格的数据从第二行开始写入
new_row_idx = 2

#遍历data文件夹中的所有Excel文件
for excel_name in os.listdir('data'):
	#打开待读取的Excel表格
    workbook = openpyxl.load_workbook('data/'+excel_name)
	#待读取表格的sheet叫作public opinion
    sheet = workbook['public opinion']
	#写入数据
    for row_idx in range(2, sheet.max_row + 1):
        new_sheet['A'+str(new_row_idx)].value = sheet['A' + str(row_idx)].value
        new_sheet['B' + str(new_row_idx)].value = sheet['B' + str(row_idx)].value
        new_sheet['C' + str(new_row_idx)].value = sheet['C' + str(row_idx)].value
        new_sheet['D' + str(new_row_idx)].value = sheet['D' + str(row_idx)].value
        new_sheet['E' + str(new_row_idx)].value = sheet['E' + str(row_idx)].value
        new_row_idx += 1

#保存修改
new_workbook.save('test.xlsx')
